﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Configuration;
using System.Data.SqlClient;

using System.Security.Cryptography;

namespace GestorDeFlotasDesktop
{
    static class SqlInterface
    {

        #region EstadoInterno
        private static SqlConnection dbConnection;
        private static string dbConnectionString;
        #endregion


        #region Conectar
        public static void Conectar()
        {
            dbConnectionString = "Data Source=localhost\\SQLSERVER2005;Initial Catalog=GD1C2012;User ID=gd;Password=gd2012";
            dbConnection = new SqlConnection(dbConnectionString);
        }
        #endregion


        #region Desconectar
        public static void Desconectar()
        {
            dbConnectionString = string.Empty;
            dbConnection.Dispose();
        }
        #endregion

        #region UserFunctions
        public static int ValidarAcceso(string usuario, string password)
        {

            SqlCommand spCmd = new SqlCommand();

            spCmd.Connection = dbConnection;
            spCmd.CommandType = CommandType.StoredProcedure;
            spCmd.CommandText = StoredProc.spValidarUsuario;
            SqlParameterCollection spCmdParams = spCmd.Parameters;
            spCmdParams.AddWithValue("@username", usuario);
            spCmdParams.AddWithValue("@password", Encriptar(password));
            dbConnection.Open();
            int result = spCmd.ExecuteNonQuery();

            spCmdParams.Clear();
            spCmd.Dispose();
            dbConnection.Close();

            return result;
        }

        public static int SumarErrorLogin(string usuario)
        {

            SqlCommand spCmd = new SqlCommand();
            spCmd.Connection = dbConnection;
            spCmd.CommandType = CommandType.StoredProcedure;
            spCmd.CommandText = StoredProc.spSumarErrorLogin;
            SqlParameterCollection spCmdParams = spCmd.Parameters;
            spCmdParams.AddWithValue("@username", usuario);
            dbConnection.Open();
            int result = Convert.ToInt32(spCmd.ExecuteScalar());

            spCmdParams.Clear();
            spCmd.Dispose();
            dbConnection.Close();

            return result;
        }
        #endregion

        #region ExecuteReader

        public static SqlDataReader ExecuteReader(string strComando)
        {
            return ExecuteReader(strComando, CommandType.StoredProcedure, null);
        }
        public static SqlDataReader ExecuteReader(string strComando, CommandType Tipo)
        {
            return ExecuteReader(strComando, Tipo, null);
        }
        public static SqlDataReader ExecuteReader(string strComando, List<SqlParameter> ParameterList)
        {
            return ExecuteReader(strComando, CommandType.StoredProcedure, ParameterList);
        }
        public static SqlDataReader ExecuteReader(string strComando, CommandType Tipo, List<SqlParameter> ParameterList)
        {
            
            SqlDataReader rdrLector = null;
            SqlCommand spCmd = new SqlCommand(strComando,dbConnection);
            SqlParameterCollection spCmdParams = spCmd.Parameters;
            spCmd.CommandType = Tipo;
            
            dbConnection.Open();
            if (ParameterList != null)
            {
                for (int i = 0; i < ParameterList.Count; i++)
                {
                    spCmdParams.AddWithValue(ParameterList[i].ParameterName, ParameterList[i].Value);
                }
            }

            rdrLector = spCmd.ExecuteReader(CommandBehavior.CloseConnection);
            spCmdParams.Clear();
            spCmd.Dispose();

            return rdrLector;
        }
        #endregion

        #region ExecuteNonQuery
        public static int ExecuteNonQuery(string strComando)
        {
            return ExecuteNonQuery(strComando, CommandType.StoredProcedure, null);
        }
        public static int ExecuteNonQuery(string strComando, CommandType Tipo)
        {
            return ExecuteNonQuery(strComando, Tipo, null);
        }
        public static int ExecuteNonQuery(string strComando, List<SqlParameter> ParameterList)
        {
            return ExecuteNonQuery(strComando, CommandType.StoredProcedure, ParameterList);
        }
        public static int ExecuteNonQuery(string strComando, CommandType Tipo, List<SqlParameter> ParameterList)
        {

            SqlCommand spCmd = new SqlCommand(strComando, dbConnection);
            spCmd.CommandType = Tipo;
            SqlParameterCollection spCmdParams = spCmd.Parameters;
            dbConnection.Open();
            if (ParameterList != null)
            {
                for (int i = 0; i < ParameterList.Count; i++)
                {
                    spCmdParams.AddWithValue(ParameterList[i].ParameterName, ParameterList[i].Value);
                }
            }
            int intRegsAffected = Convert.ToInt32(spCmd.ExecuteNonQuery());
            
            spCmdParams.Clear();
            spCmd.Dispose();
            dbConnection.Close();

            return intRegsAffected;
        }
        #endregion


        #region BusinessFunctions
        
        public static int IngresarViaje(int chofer, int turno, int cliente,int tipo,int fichas,DateTime fecha)
        {
            SqlCommand spCmd = new SqlCommand();
            spCmd.Connection = dbConnection;
            spCmd.CommandType = CommandType.StoredProcedure;
            spCmd.CommandText = StoredProc.spIngresarViaje;

            SqlParameterCollection spCmdParams = spCmd.Parameters;
            spCmdParams.AddWithValue("@chofer", chofer);
            spCmdParams.AddWithValue("@turno", turno);
            spCmdParams.AddWithValue("@cliente", cliente);
            spCmdParams.AddWithValue("@tipo", tipo);
            spCmdParams.AddWithValue("@fichas", fichas);
            spCmdParams.Add ("@fecha", SqlDbType.DateTime).Value = fecha;

            dbConnection.Open();
            int result = spCmd.ExecuteNonQuery();
            
            spCmdParams.Clear();
            spCmd.Dispose();
            dbConnection.Close();
            return result;
        }

        public static bool ExisteChofer(int chofer)
        {
            SqlCommand spCmd = new SqlCommand();
            spCmd.Connection = dbConnection;
            spCmd.CommandType = CommandType.StoredProcedure;
            spCmd.CommandText = StoredProc.spExisteChofer;

            SqlParameterCollection spCmdParams = spCmd.Parameters;
            spCmdParams.AddWithValue("@chofer", chofer);

            dbConnection.Open();
            int result = spCmd.ExecuteNonQuery();
            spCmdParams.Clear();
            spCmd.Dispose();
            dbConnection.Close();

            if (result > 0)
                return true;
            else
                return false;
        }

        public static bool ExisteCliente(int cliente)
        {
            SqlCommand spCmd = new SqlCommand();
            spCmd.Connection = dbConnection;
            spCmd.CommandType = CommandType.StoredProcedure;
            spCmd.CommandText = StoredProc.spExisteCliente;

            SqlParameterCollection spCmdParams = spCmd.Parameters;
            spCmdParams.AddWithValue("@cliente", cliente);

            dbConnection.Open();
            int result = spCmd.ExecuteNonQuery();
            spCmdParams.Clear();
            spCmd.Dispose();
            dbConnection.Close();

            if (result > 0)
                return true;
            else
                return false;
        }

        #endregion

        #region StoredProcedures
        static class StoredProc
        {
            public static string spValidarUsuario = "SQUIRTLE.spValidarUsuario";
            public static string spSumarErrorLogin = "SQUIRTLE.spSumarErrorLogin";
            public static string spIngresarViaje = "SQUIRTLE.spIngresarViaje";

            public static string spExisteChofer = "SQUIRTLE.spExisteChofer";
            public static string spExisteCliente = "SQUIRTLE.spExisteCliente";
        }
        #endregion

        #region Criptografia
        public static string Encriptar(string password)
        {
            SHA256Managed passwd = new SHA256Managed();
            byte[] asciiPassword = System.Text.Encoding.ASCII.GetBytes(password);
            byte[] cipheredPassword = passwd.ComputeHash(asciiPassword);
            string passwordString = Convert.ToBase64String(cipheredPassword);
            return passwordString;
        }
        #endregion
    }
}
